import java.sql.*;
import java.util.*;

public class Administrator
{
	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;
	private String query;

	public Administrator(Connection c, Statement s)
	{
		connection = c;
		statement = s;
		ConsoleMenu();	
	}
	
	public void ConsoleMenu()
	{
		int swValue;
		boolean Exit;
		
		Exit = false;
		while (!Exit)
		{
			System.out.println("==========================================");
			System.out.println("|   ADMINISTRATORS                       |");
			System.out.println("==========================================");
			System.out.println("| Options:                               |");
			System.out.println("|        1. New Registration             |");
			System.out.println("|        2. Update Date                  |");
			System.out.println("|        3. Product Statistics           |");
			System.out.println("|        4. Statistics                   |");
			System.out.println("|        5. Exit                         |");
			System.out.println("==========================================");
			swValue = Keyin.inInt(" Select option: ");
			
			switch (swValue)
			{
				case 1:
				  NewRegistration();
				  break;
				case 2:
				  UpdateDate();
				  break;
				case 3:
				  ProductStatistics();
				  break;
				case 4:
				  Statistics();
				  break;
				case 5:
				  System.out.println("Exit selected");
				  Exit = true;
				  break;
				default:
				  System.out.println("Invalid selection");
				  break; // This break is not really necessary
			}
		}
	}
	
	public void Statistics()
	{
		String temp;	
		ArrayList<String> LeafCategories = new ArrayList<String>();
		ArrayList<Integer> LeafCategoriesCounts = new ArrayList<Integer>();
		ArrayList<String> ParentCategories = new ArrayList<String>();
		ArrayList<String> RootCategories = new ArrayList<String>();
		ArrayList<Integer> RootCategoriesCounts = new ArrayList<Integer>();
		ArrayList<String> Descriptors = new ArrayList<String>();
		ArrayList<Integer> Counts = new ArrayList<Integer>();
		
		try
		{				
			int pastMonths = Keyin.inInt(" Enter x(statistics for the past x months): ");
			int highest = Keyin.inInt(" Enter k(top k highest volume LeafCategories): ");		
		
			query = "SELECT ParentCatalog FROM CATALOG";
			resultSet = statement.executeQuery(query);
			while (resultSet.next())
			{
				ParentCategories.add(resultSet.getString(1));
			}
			query = "SELECT Name, Product_Count(Name, "+pastMonths+") FROM CATALOG";
			resultSet = statement.executeQuery(query);
			while (resultSet.next())
			{
				temp = resultSet.getString(1);
				if (!ParentCategories.contains(temp))
				{
					LeafCategories.add(temp);
					LeafCategoriesCounts.add(resultSet.getInt(2));
				}
			}
		
			sort(LeafCategories, LeafCategoriesCounts);
			printk("Top "+highest+" highest volume leaf categories\tCount of products sold", LeafCategories, LeafCategoriesCounts, highest);
			
			query = "SELECT Name, Product_Count(Name, "+pastMonths+") FROM CATALOG WHERE ParentCatalog is null";
			resultSet = statement.executeQuery(query);
			while (resultSet.next())
			{
				RootCategories.add(resultSet.getString(1));
				RootCategoriesCounts.add(resultSet.getInt(2));
			}								
			
			sort(RootCategories, RootCategoriesCounts);
			printk("Top "+highest+" highest volume root categories\tCount of products sold", RootCategories, RootCategoriesCounts, highest);
			
			Descriptors.clear();
			Counts.clear();
			query = "SELECT Login, Bid_Count(Login, "+pastMonths+") FROM CUSTOMER";
			resultSet = statement.executeQuery(query);
			while (resultSet.next())
			{
				Descriptors.add(resultSet.getString(1));
				Counts.add(resultSet.getInt(2));
			}
			
			sort(Descriptors, Counts);
			printk("Top "+highest+" most active bidders\tCount of bids placed", Descriptors, Counts, highest);
			
			Descriptors.clear();
			Counts.clear();			
			query = "SELECT Login, Buying_Amount(Login, "+pastMonths+") FROM CUSTOMER";
			resultSet = statement.executeQuery(query);
			while (resultSet.next())
			{
				Descriptors.add(resultSet.getString(1));
				Counts.add(resultSet.getInt(2));
			}
			sort(Descriptors, Counts);
			printk("Top "+highest+" most active buyers\tTotal dollar amount spent", Descriptors, Counts, highest);			
			
			connection.commit();
		}
		catch(Exception Ex)
		{
		  System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
		}								
	}
	
	public void sort(ArrayList<String> Descriptors, ArrayList<Integer> Counts)
	{
		int i;
		int j;
		String tempS;
		Integer tempI;
		
		for (i=0;i<Descriptors.size();i++)
			for (j=i+1;j<Descriptors.size();j++)
			if (Counts.get(i)<Counts.get(j))
			{
				tempS = Descriptors.get(i);
				Descriptors.set(i, Descriptors.get(j));
				Descriptors.set(j, tempS);
				tempI = Counts.get(i);
				Counts.set(i, Counts.get(j));
				Counts.set(j, tempI);
			}
	}
	
	public void printk(String prompt, ArrayList<String> Descriptors, ArrayList<Integer> Counts, int k)
	{
		if (k>Counts.size())
		{
			k = Counts.size();
		}
	
		System.out.println(prompt);
		for (int i=0;i<Descriptors.size();i++)
		if (Counts.get(i)>=Counts.get(k-1))
		{
			System.out.println(Descriptors.get(i)+"\t"+Counts.get(i));
		}
		System.out.println();
	}
	
	//alter session set nls_date_format = 'DD-mon-YY/HH:MI:SSam';
	public void UpdateDate()
	{
		try
		{		
			java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("dd-MMM-yy/hh:mm:ssa");			
			java.sql.Time date;
			
			String dateString = Keyin.inString(" Enter new date(\"11-dec-07/12:00:00am\", \"Now\"): ");			
			if (dateString.equals("Now"))
			{
				date = new java.sql.Time(java.util.Calendar.getInstance().getTime().getTime());
			}
			else
			{
				date = new java.sql.Time(df.parse(dateString).getTime());
			}
			
			query = "update OURDATE set C_DATE = ?";
			
			PreparedStatement updateStatement = connection.prepareStatement(query);
			updateStatement.setTime(1, date);
			updateStatement.executeUpdate();
			
			connection.commit();
		}
		catch(Exception Ex)
		{
		  System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
		}										
	}	
	
	public void ProductStatistics()
	{			
		String Status;
		String Name;
		int Amount;
		String Buyer = "";
		int AuctionID;
		ResultSet tempSet;
		int tempAmount;
		String option;
		int count = 0;
		
		ArrayList<Integer> AuctionIDs = new ArrayList<Integer>();
		ArrayList<String> Bidders = new ArrayList<String>();
		ArrayList<Integer> Amounts = new ArrayList<Integer>();
			
		try
		{
			query = "SELECT AuctionID, Bidder, Amount FROM BIDLOG";
			resultSet = statement.executeQuery(query);
			while (resultSet.next())
			{
				AuctionIDs.add(resultSet.getInt(1));
				Bidders.add(resultSet.getString(2));
				Amounts.add(resultSet.getInt(3));
			}		
			
			option = Keyin.inString(" Listing products from a specified customer(\"user\", \"all\"): ");
			
			if (option.equals("all"))
			{
				query = "SELECT AuctionID, Name, Status, Amount, Buyer FROM PRODUCT";
				resultSet = statement.executeQuery(query);
			}
			else
			{
				query = "SELECT AuctionID, Name, Status, Amount, Buyer FROM PRODUCT WHERE Seller='"+option+"'";
				resultSet = statement.executeQuery(query);
			}
			
			while (resultSet.next())
			{
				count++;
			
				AuctionID = resultSet.getInt(1);
				Name = resultSet.getString(2);
				Status = resultSet.getString(3);
				if (Status.equals("sold"))
				{
					Amount = resultSet.getInt(4);
					Buyer = resultSet.getString(5);
					System.out.println("Product Name: "+Name+"\tStatus: "+Status+"\tHighest Bid Amount: "+Amount+"\tBuyer Login Name: "+Buyer);
				}
				else if (Status.equals("underauction"))
				{
					Amount = Integer.MIN_VALUE;
					for (int i=0;i<AuctionIDs.size();i++)
					{
						if (AuctionIDs.get(i)==AuctionID&&Amounts.get(i)>Amount)
						{
							Amount = Amounts.get(i);
							Buyer = Bidders.get(i);
						}
					}
					if (Amount!=Integer.MIN_VALUE)
					{
						System.out.println("Product Name: "+Name+"\tStatus: "+Status+"\tHighest Bid Amount: "+Amount+"\tBidder Login Name: "+Buyer);
					}
					else
					{
						System.out.println("Product Name: "+Name+"\tStatus: "+Status+"\tNo Bidding");
					}
				}
				else
				{
					System.out.println("Product Name: "+Name+"\tStatus: "+Status);
				}
			}		
			
			if (count==0)
			{
				System.out.println("No products.");
			}
		
			connection.commit();
		}
		catch(Exception Ex)
		{
		  System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
		}												
	}
	
	public void NewRegistration()
	{
		ArrayList<String> Logins = new ArrayList<String>();
		String Login;
	
		try
		{		
			query = "SELECT Login FROM ADMINISTRATOR FOR UPDATE";
			resultSet = statement.executeQuery(query);
			
			while (resultSet.next())
			{
				Login = resultSet.getString(1);
				Logins.add(Login);
			}		
			
			query = "SELECT Login FROM CUSTOMER FOR UPDATE";
			resultSet = statement.executeQuery(query);
			
			while (resultSet.next())
			{
				Login = resultSet.getString(1);
				Logins.add(Login);
			}			
			
			while (true)
			{
				Login = Keyin.inString(" Enter login name: ");
				if (Logins.contains(Login))
				{
					Keyin.printlnPrompt(" Login name unavailable.");
				}
				else
				{
					break;
				}
			}
			
			String Password = Keyin.inString(" Enter password: ");			
			String Name = Keyin.inString(" Enter name: ");
			String Address = Keyin.inString(" Enter address: ");
			String Email = Keyin.inString(" Enter email: ");		
			char admin = Keyin.inChar(" Whether the new user is an administrator(y/n)? ");
			
			if (admin=='y')
			{
				query = "insert into ADMINISTRATOR values(?, ?, ?, ?, ?)";				
			}
			else
			{
				query = "insert into CUSTOMER values(?, ?, ?, ?, ?)";
			}
			
			PreparedStatement updateStatement = connection.prepareStatement(query);
			updateStatement.setString(1, Login);
			updateStatement.setString(2, Password);
			updateStatement.setString(3, Name);
			updateStatement.setString(4, Address);
			updateStatement.setString(5, Email);
			updateStatement.executeUpdate();
					
			connection.commit();
		}
		catch(Exception Ex)
		{
		  System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
		}								
	}
				
	public static void main(String[] args)
	{
		String username, password;
		
		username = Keyin.inString(" Enter username: ");
		password = Keyin.inString(" Enter password: ");
	
		Connection c = ConnectionManager.getConnection(username, password);		
		Statement s = ConnectionManager.createStatement(c);
		Administrator admin = new Administrator(c, s);
		ConnectionManager.closeConnection(c);
	}
}
